
Create   proc sp_update_balance_qty_for_cancel_ps_gp
@lot_id bigint,
@pull_qty int
As
begin
	declare @cslnt_qty_pull_existing_qty int
	declare @il_qty_balance_existing int

	select @il_qty_balance_existing = IL_Qty_Balance from Inventory_lot where Lot_Id = @lot_id
	
	begin
		update Inventory_Lot set IL_Qty_Balance = @pull_qty + @il_qty_balance_existing where Lot_ID = @lot_id
	end

	set @il_qty_balance_existing=-1

	select @il_qty_balance_existing = IL_Qty_Balance from Inventory_lot where Lot_Id = @lot_id

	if @il_qty_balance_existing <= 0
	begin
		update Lot_Table set lot_current_status = 'Close' where Lot_ID = @lot_id
	end
	else
	begin
		update Lot_Table set lot_current_status = 'Running' where Lot_ID = @lot_id
	end

end
